﻿CREATE procedure [dbo].[spReportApplicationBrazilProjects]
	@yearPosition int,
	@date datetime
AS

DECLARE @year int
SET @year = YEAR(@date) + @yearPosition

DECLARE @query varchar(max) 

SET @query = 'select DISTINCT ' + CONVERT(varchar(4), @year) + ' as [Period Year], AccountLevel1 as [Account Level 1], AccountLevel2 as [Account Level 2]
	, AccountLevel3 as [Account Level 3], InitiativeId as [Initiative Id], InitiativeName as [Initiative Name]
	, ProgramId as [Program Id], ProgramName as [Program Name], ProjectId as [Project Id], ProjectName as [Project Name]
	, ProgramManager as [Program Manager], ProgramStartDate as [Program Start Date], ProgramEndDate as [Program End Date]
	, ProjectStatus as [Project Status], ProjectStartDate as [Project Start Date], ProjectEndDate as [Project End Date]
	, ApplicationId as [Application Id], ApplicationName as [Application Name], ApplicationRetirementDate as [Application Retirement Date]
	, ProjectManager as [Project Manager], BillingClientGocCode as [Billing Client Goc Code], Client3Name as [Client 3 Name]
	, Client4Name as [Client 4 Name], Client5Name as [Client 5 Name], Client6Name as [Client 6 Name], Client7Name as [Client 7 Name]
	, Client8Name as [Client 8 Name], Client9Name as [Client 9 Name], Client10Name as [Client 10 Name], ClientGocCode as [Client Goc Code]
	, ClientGOC.ManagedGeography as [Client Geography Code], ClientGOC.ManagedGeographyDescription as [Client Geography Description]
	, System5Name as [System 5 Name], System6Name as [System 6 Name], System7Name as [System 7 Name], System8Name as [System 8 Name]
	, System9Name as [System 9 Name], System10Name as [System 10 Name], SystemGOCCode as [System Goc Code]
	, SystemGOC.ManagedGeography as [System Geography Code], SystemGOC.ManagedGeographyDescription as [System Geography Description]
	, SystemRegion as [System Region], LOB as [LOB], RuntheBankBuildtheBank as [Run the Bank Build the Bank]
	, ProgramProjectLinkIndicator as [Program Project Link Indicator], WorkTypeFinancials as [Work Type Financials]
	, InitiativeInvestimentCategory as [Init Invest Category], ProgramInvestimentCategory as [Prog Invest Category]
	, ProgramCapitalizedIndicator as [Prog Capind], ProgramStatus as [Program Status], ProjectCapitalization as [Project Capitalization]
	, BowFlag as [Bow Flag], BowFlag2 as [Bow Flag2], ProjectType as [Project Type], ProgramCarryoverIndicator as [Program Carryover Indicator]
	, TaxonomyName as [Taxonomy Name], CURate as [Cu Rate], FirstOfProgramDesc as [First of Program Desc], LobParent as [Lob Parent]
	, AdjustedFlag as [Adjusted Flag], January as [January Bow], February as [February Bow], March as [March Bow], April as [April Bow]
	, May as [May Bow], June as [June Bow], July as [July Bow], August as [August Bow], September as [September Bow], October as [October Bow]
	, November as [November Bow], December as [December Bow], YearToDate as [Year To date Bow], FullYear as [Full Year Bow] 
from ApplicationBookOfWork' + CONVERT(varchar(4), @year) + ' Application
	left join GOC as ClientGOC on ClientGOC.GocAttributes = ClientGocCode 
	left join GOC as SystemGOC on SystemGOC.GocAttributes = SystemGocCode 
Where (ClientGOC.ManagedGeography = ''BR'' OR SystemGOC.ManagedGeography = ''BR'') 
	AND Application.ReferenceDate = ''' + CONVERT(varchar(20), @date) + '''' 

EXEC(@query)